package jst.householdmanager.database.mysql;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import jst.householdmanager.database.ItemCategoriesDataSource;
import jst.householdmanager.database.ItemsDataSource;
import jst.householdmanager.database.Items_in_ItemCategoriesDataSource;
import jst.householdmanager.database.PurchaseListPosDataSource;
import jst.householdmanager.database.SQLiteDataSource;
import jst.householdmanager.database.ShopsDataSource;
import jst.householdmanager.database.Shops_has_ItemsDataSource;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.os.Environment;
import android.util.Log;

public class SyncWithMysql {
	public enum SyncDirection {
		TOMYSQL, FROMMYSQL;
	}

	private SyncDirection syncDirection;
	private Context context;
	private Connection mysqlConn;

	public SyncWithMysql(SyncDirection syncDirection, Context context) {
		super();
		this.syncDirection = syncDirection;
		this.context = context;
		this.mysqlConn = MySQLDatabase.getInstance();
	}

	public String startSync() {
		String errorMsg = null;

		if (this.mysqlConn != null) {

			// call pre processing
			preSync();

			// process all tables
			if (errorMsg == null)
				errorMsg = syncTable(ItemCategoriesDataSource.TABLE_NAME, ItemCategoriesDataSource.ALL_COLUMNS,
						ItemCategoriesDataSource.COLUMN_DATATYPE);
			if (errorMsg == null)
				errorMsg = syncTable(ItemsDataSource.TABLE_NAME, ItemsDataSource.ALL_COLUMNS, ItemsDataSource.COLUMN_DATATYPE);
			if (errorMsg == null)
				errorMsg = syncTable(Items_in_ItemCategoriesDataSource.TABLE_NAME, Items_in_ItemCategoriesDataSource.ALL_COLUMNS,
						Items_in_ItemCategoriesDataSource.COLUMN_DATATYPE);
			if (errorMsg == null)
				errorMsg = syncTable(ShopsDataSource.TABLE_NAME, ShopsDataSource.ALL_COLUMNS, ShopsDataSource.COLUMN_DATATYPE);
			if (errorMsg == null)
				errorMsg = syncTable(Shops_has_ItemsDataSource.TABLE_NAME, Shops_has_ItemsDataSource.ALL_COLUMNS,
						Shops_has_ItemsDataSource.COLUMN_DATATYPE);
			if (errorMsg == null)
				errorMsg = syncTable(PurchaseListPosDataSource.TABLE_NAME, PurchaseListPosDataSource.ALL_COLUMNS,
						PurchaseListPosDataSource.COLUMN_DATATYPE);

			// call post processing
			postSync();

		} else {
			errorMsg = "Error no MySQL Connection available!";
		}

		return errorMsg;
	}

	private String preSync() {
		String errorMsg = null;

		switch (this.syncDirection) {
		// sync to MySQL
		case TOMYSQL:
			// disable foreign key check
			try {
				PreparedStatement preparedStatement = this.mysqlConn.prepareStatement("SET FOREIGN_KEY_CHECKS=0");
				preparedStatement.executeUpdate();
			} catch (SQLException e) {
				errorMsg = "Error while disable foreign_key_check!";
				e.printStackTrace();
			}
			break;
		// sync from MySQL
		case FROMMYSQL:
			try {
				this.sqliteBackup();
			} catch (IOException e) {
				errorMsg = "Error while creating SQLite Backup!";
				e.printStackTrace();
			}
			break;
		}

		return errorMsg;
	}

	private String syncTable(String tableName, String[] allColumnsArray, int[] columnsType) {
		String errorMsg = null;

		// prepare Statements
		StringBuffer allColumnsBuffer = new StringBuffer();
		StringBuffer insertFieldBuffer = new StringBuffer();
		boolean comma = false;
		for (String column : allColumnsArray) {
			if (comma) {
				allColumnsBuffer.append(", ");
				insertFieldBuffer.append(", ");
			} else {
				comma = true;
			}
			allColumnsBuffer.append(column);
			insertFieldBuffer.append("?");
		}
		String allColumns = allColumnsBuffer.toString();
		String insertField = insertFieldBuffer.toString();

		switch (syncDirection) {
		// sync to MySQL
		case TOMYSQL:
			errorMsg = syncTableToMySQL(tableName, allColumnsArray, columnsType, allColumns, insertField);
			break;
		// sync from MySQL
		case FROMMYSQL:
			errorMsg = syncTableFromMySQL(tableName, allColumnsArray, columnsType, allColumns, insertField);
			break;
		}

		return errorMsg;
	}

	private String postSync() {
		String errorMsg = null;

		switch (this.syncDirection) {
		// sync to MySQL
		case TOMYSQL:
			// enable foreign key check
			try {
				PreparedStatement preparedStatement = this.mysqlConn.prepareStatement("SET FOREIGN_KEY_CHECKS=1");
				preparedStatement.executeUpdate();
			} catch (SQLException e) {
				errorMsg = "Error while enable foreign_key_check!";
				e.printStackTrace();
			}
			break;
		// sync from MySQL
		case FROMMYSQL:
			break;
		}

		return errorMsg;
	}

	private String syncTableToMySQL(String tableName, String[] allColumnsArray, int[] columnsType, String allColumns, String insertField) {

		// delete mysql tablerows
		try {
			PreparedStatement preparedStatement = this.mysqlConn.prepareStatement("TRUNCATE TABLE " + MySQLDatabase.tablePrefix + tableName);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return "Error while clearing MySQL Table: " + MySQLDatabase.tablePrefix + tableName;
		}

		int insertsRows = 0;

		// query SQLite Table
		Cursor cursor = SQLiteDataSource.queryDatabase("SELECT " + allColumns + " FROM " + tableName);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			try {
				String insertQuery = "INSERT INTO " + MySQLDatabase.tablePrefix + tableName + "(" + allColumns + ") VALUES (" + insertField + ")";
				PreparedStatement preparedStatement = this.mysqlConn.prepareStatement(insertQuery);
				for (int i = 0; i < allColumnsArray.length; i++) {
					switch (columnsType[i]) {
					// integer
					case 0:
						preparedStatement.setInt(i + 1, cursor.getInt(i));
						break;
					// string
					case 1:
						preparedStatement.setString(i + 1, cursor.getString(i));
						break;
					}
				}
				preparedStatement.executeUpdate();
				insertsRows++;
			} catch (SQLException e) {
				e.printStackTrace();
				cursor.close();
				return "Error while writing into MySQL Table: " + MySQLDatabase.tablePrefix + tableName;
			}
			cursor.moveToNext();
		}
		cursor.close();

		Log.d("JST-DB", "SQLite -> MySQL: " + MySQLDatabase.tablePrefix + tableName + " rows: " + Integer.toString(insertsRows));

		return null;

	}

	private String syncTableFromMySQL(String tableName, String[] allColumnsArray, int[] columnsType, String allColumns, String insertField) {

		// delete sqlite tablerows
		SQLiteDataSource.queryDatabase("DELETE FROM " + tableName);
		SQLiteDataSource.queryDatabase("DELETE FROM sqlite_sequence WHERE name='" + tableName + "'");

		int insertsRows = 0;

		// query MySQL Table
		Statement query;
		try {
			query = this.mysqlConn.createStatement();
			ResultSet result = query.executeQuery("SELECT " + allColumns + " FROM " + MySQLDatabase.tablePrefix + tableName);
			while (result.next()) {
				ContentValues values = new ContentValues();
				for (int i = 0; i < allColumnsArray.length; i++) {
					switch (columnsType[i]) {
					// integer
					case 0:
						values.put(allColumnsArray[i], result.getString(i + 1));
						break;
					// string
					case 1:
						values.put(allColumnsArray[i], result.getString(i + 1));
						break;
					}
				}
				SQLiteDataSource.getDatabase().insert(tableName, null, values);
				insertsRows++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return "Error while reading from " + MySQLDatabase.tablePrefix + tableName;
		}
		Log.d("JST-DB", "MySQL -> SQLite: " + tableName + " rows: " + Integer.toString(insertsRows));

		return null;

	}

	private void sqliteBackup() throws IOException {
		final String inFileName = this.context.getDatabasePath("householdmanager.db").getPath();
		File dbFile = new File(inFileName);
		FileInputStream fis = new FileInputStream(dbFile);

		String outFileName = Environment.getExternalStorageDirectory() + "/OwnBackups/householdmanager_copy.db";

		// Open the empty db as the output stream
		OutputStream output = new FileOutputStream(outFileName);

		// Transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = fis.read(buffer)) > 0) {
			output.write(buffer, 0, length);
		}

		// Close the streams
		output.flush();
		output.close();
		fis.close();
	}

}
